package cn.itcast.bos.web.action.base;

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import org.apache.struts2.convention.annotation.Results;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Controller;

import cn.itcast.bos.domain.base.Area;
import cn.itcast.bos.domain.base.SubArea;
import cn.itcast.bos.service.base.AreaService;
import cn.itcast.bos.service.base.SubAreaService;
import cn.itcast.bos.utils.FileUtils;
import cn.itcast.bos.web.action.common.BaseAction;

@Controller
@Scope("prototype")
@Namespace("/")//<package namespace extends
@ParentPackage("struts-default")
@Results({@Result(name="list",type="redirect",location="/pages/base/sub_area.jsp")})
public class SubAreaAction extends BaseAction<SubArea> {
	

	
	@Autowired
	private SubAreaService subAreaService;
	
	@Autowired
	private AreaService areaService;
	
	//定义变量接收上传文件
	private File upload;
	
	public void setUpload(File upload) {
		this.upload = upload;
	}
	
	@Action("subareaAction_save")
	public String save() throws Exception {
		subAreaService.save(model);
		return "list";
	}
	
	@Action("subAreaAction_pageQuery")
	public String pageQuery() throws Exception {
		Pageable pageable = new PageRequest(page-1, rows);
		Page<SubArea> page = subAreaService.pageQuery(pageable);
		//subareas集合属性来源分区对象所属区域
		//TODO 将来分区跟定区建立关系。此方法会no-session问题
		this.java2Json(page, new String[]{"subareas","couriers"});
		return NONE;
		
	}
	
	/**
	  * @Description: 未关联到定区分区记录
	  * @return json数组
	  * @throws Exception
	  *	  
	 */
	@Action("subAreaAction_findAll")
	public String findAll() throws Exception {
		List<SubArea> list=subAreaService.findNotAssociation();
		this.java2Json(list, new String[]{"subareas"});
		return NONE;
	}
	
	
	
	/**
	  * @Description: 查询分区记录，将查询结果写入excel文件，将文件下载
	 */
	@Action("subAreaAction_exportXls")
	public String exportXls() throws Exception {
		List<SubArea> list = subAreaService.findAll();
		//创建空白POI excel文件对象
		//XSSFWorkbook; 07
		HSSFWorkbook workbook = new HSSFWorkbook();
		//创建标签页
		HSSFSheet sheet = workbook.createSheet("分区数据");
		//创建行  1、创建标题行   2、创建数据行
		HSSFRow headRow = sheet.createRow(0);
		//创建单元格--写入单元格数据
		headRow.createCell(0).setCellValue("编号");
		headRow.createCell(1).setCellValue("关键字");
		headRow.createCell(2).setCellValue("辅助关键字");
		headRow.createCell(3).setCellValue("区域信息");
		headRow.createCell(4).setCellValue("定区信息");
		HSSFRow dataRow;
		for (SubArea subArea : list) {
			dataRow=sheet.createRow(sheet.getLastRowNum()+1);//sheet.getLastRowNum()获取当前sheet的被写上内容的最后一行，若想再空白一行添加数据则加1                                      
			dataRow.createCell(0).setCellValue(subArea.getId());
			dataRow.createCell(1).setCellValue(subArea.getKeyWords());
			dataRow.createCell(2).setCellValue(subArea.getAssistKeyWords());
			dataRow.createCell(3).setCellValue(subArea.getArea().getName());
			dataRow.createCell(4).setCellValue(subArea.getFixedArea().getFixedAreaName());
		}
		HttpServletResponse response = ServletActionContext.getResponse();
		//文件下载：一个流 两个头（1、文件MIME类型  2、文件打开方式（浏览器内部打开，作为附件下载））
		String filename="分区记录.xls";
		String agent = ServletActionContext.getRequest().getHeader("User-Agent");
		//对文件名进行编码
		filename = FileUtils.encodeDownloadFilename(filename, agent);
		response.setHeader("content-type", "application/vnd.ms-excel");
		response.setHeader("content-disposition", "attachment;filename="+filename);
		OutputStream stream = response.getOutputStream();
		workbook.write(stream);
		return NONE;
	}
	
	
	/**
	  * @Description: 根据省份统计各省份下分区数量
	  * @return 二维数组
	  [
       ['北京市',   45],
       ['河北省',   268],
       ['天津市',   85],
       ['河南省',   62]
     ]
	 */
	@Action("subAreaAction_showChart")
	public String showChart() throws Exception {
		List<Object[]> list = subAreaService.showChart();
		this.java2Json(list, null);
		return NONE;
	}
	
	
	public static void main(String[] args) {
		List<Object[]> list = new ArrayList<>();
		
		Object[] obj1 = new Object[2];
		obj1[0] = "北京市";
		obj1[1] = 120;
		
		Object[] obj2 = new Object[2];
		obj2[0] = "河北省";
		obj2[1] = 268;
		
		Object[] obj3 = new Object[2];
		obj3[0] = "天津市";
		obj3[1] = 85;
		
		list.add(obj1);
		list.add(obj2);
		list.add(obj3);
		String ret = JSONArray.fromObject(list).toString();
		System.out.println(ret);
	}

	 private String fixedAreaId;
	 

		public void setFixedAreaId(String fixedAreaId) {
		this.fixedAreaId = fixedAreaId;
	}

		@Action("subareaAreaAction_pageQuerySubarea")
		public String pageQuerySubarea() throws Exception {
			Pageable pageable = new PageRequest(page-1, rows);
			Page<SubArea> page = subAreaService.pageQuery(pageable,fixedAreaId);
			this.java2Json(page, new String[]{"subareas","couriers"});
			return NONE;
			 
		} 

	
	
	
	
	

	
	/**
	  * @throws Exception 
	 * @Description: 通过POI解析excel文件中数据
	 */
	@Action("subAreaAction_importXls")
	public String subAreaAction() throws  Exception{
		List<SubArea>list=new ArrayList<>();
		//创建excel文件对象
		//该表为07excel表 . 需要使用XSSFWorkbook
		XSSFWorkbook workbook=new XSSFWorkbook(new FileInputStream(upload));
		//获取标签页
		XSSFSheet sheet =workbook.getSheetAt(0);
		//遍历标签页获取行
		for (Row row : sheet) {
			//忽略标题行
			if(row.getRowNum()==0){
				continue;
			}
			String id=row.getCell(0).getStringCellValue();
			String province=row.getCell(1).getStringCellValue();
			String city=row.getCell(2).getStringCellValue();
			String district=row.getCell(3).getStringCellValue();
			String keyWords=row.getCell(4).getStringCellValue();
			String startNum=row.getCell(5).getStringCellValue();
			String endNum=row.getCell(6).getStringCellValue();
			String single =row.getCell(7).getStringCellValue();
			String assistKeyWords=row.getCell(8).getStringCellValue();
			//通过分区表的属性获取区域表的对象;
			Area area=areaService.findByArea(province, city, district);
			//将区域对象添加到区域集合中
			//创建分区对象
			SubArea subArea=new SubArea(id, startNum, endNum, single, keyWords, assistKeyWords, area, null);
			//将分区对象添加到分区集合中
			list.add(subArea);
		}
			subAreaService.save(list);
				//释放资源
				workbook.close();
				//由于提交表单到Iframe中，故配置结果视图也看不到
			return NONE;
	}
	
	
	
	

}
